package
{
	import flash.utils.ByteArray;
	import flash.data.*;
	import flash.events.SQLEvent;
	import flash.events.SQLErrorEvent;
	import flash.filesystem.File; 
	
	[Bindable]
	[Table(name='projects')]
	dynamic class Projects extends Object
	{
		public var id:int;
		public var name:String;
		public var datestart:String;
		public var dateend:String;
		public var active:Boolean;
		public var billable:Boolean;
		public var clientid:int;
		public var client:String;
		public var localid:int;

		private static var dbFile:File;
		private static var conn:SQLConnection;
		
		public function Projects()
		{
			if (conn == null) 
			{
				conn = new SQLConnection(); 
					 
				conn.addEventListener(SQLEvent.OPEN, openHandler); 
				conn.addEventListener(SQLEvent.RESULT, getResults); 
				conn.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
					 
				// The database file is in the application storage directory 
				dbFile = File.applicationStorageDirectory.resolvePath("projects.db"); 
				
				if(!dbFile.exists)
				{
					conn.open(dbFile, SQLMode.CREATE);
					conn.close();
				}
				conn.open(dbFile, SQLMode.UPDATE);
				
				var sqlStatement:SQLStatement = new SQLStatement();
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
				sqlStatement.sqlConnection = conn;
				sqlStatement.text = 'Create Table If Not Exists projects(id INTEGER unique, name TEXT, datestart TEXT, dateend TEXT, active BOOLEAN, billable BOOLEAN, clientid INTEGER, client TEXT, localid INTEGER)';
				sqlStatement.execute();
				localid = 0;
			}
		}
		
		public function getResults(e:SQLEvent):void
		{
			//trace(e.target.data);
		}
		
		public function execute_query(sql)
		{
			Projects(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.sqlConnection = conn;
			sqlStatement.text = sql;
			sqlStatement.execute();
			return sqlStatement.getResult().data;
		}
		
		public function insert()
		{
			Projects(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;

			sqlStatement.text = 'Insert Or Replace Into projects Values(@ID, @NAME, @DATESTART, @DATEEND, @ACTIVE, @BILLABLE, @CLIENTID, @CLIENT, @LOCALID)';
			sqlStatement.parameters['@ID'] = id;
			sqlStatement.parameters['@NAME'] = name;
			sqlStatement.parameters['@DATESTART'] = datestart;
			sqlStatement.parameters['@DATEEND'] = dateend;
			sqlStatement.parameters['@ACTIVE'] = active;
			sqlStatement.parameters['@BILLABLE'] = billable;
			sqlStatement.parameters['@CLIENTID'] = clientid;
			sqlStatement.parameters['@CLIENT'] = client;
			sqlStatement.parameters['@LOCALID'] = localid;
			sqlStatement.execute();
		}
		
		public function bulk_insert(projects)
		{
			Projects(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;

			sqlStatement.text = 'Insert Or Replace Into projects Values(@ID, @NAME, @DATESTART, @DATEEND, @ACTIVE, @BILLABLE, @CLIENTID, @CLIENT, @LOCALID)';
			conn.begin();
			for each(var p in projects.project.*)
			{
				sqlStatement.parameters['@ID'] = int(p.id);
				sqlStatement.parameters['@NAME'] = p.name;
				sqlStatement.parameters['@DATESTART'] = p.datestart;
				sqlStatement.parameters['@DATEEND'] = p.dateend;
				sqlStatement.parameters['@ACTIVE'] = (p.active=='t');
				sqlStatement.parameters['@BILLABLE'] = (p.billable=='t');
				sqlStatement.parameters['@CLIENTID'] = int(p.clientid);
				sqlStatement.parameters['@CLIENT'] = p.client;
				sqlStatement.parameters['@LOCALID'] = int(p.localid);
				sqlStatement.execute();
			}
			conn.commit();
		}
		
		public function update()
		{
			
		}
		
		public function get_project(projectid)
		{
			Projects(null);
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			sqlStatement.sqlConnection = conn;
			sqlStatement.text = 'Select * From projects Where id = '+projectid; //@LOCALID';
			//sqlStatement.parameters['@LOCALID'] = task_number;
			sqlStatement.execute();
			var _data = sqlStatement.getResult().data;
			trace(_data);
			try
			{
				set_details(_data[0]);
			}
			catch(e){}
			return(_data);
		}
			 
		function openHandler(event:SQLEvent):void 
		{ 
			//trace("the database was created successfully"); 
		} 
			 
		function errorHandler(event:SQLErrorEvent):void 
		{ 
			new Errors("Error message: "+event.error.message+"\nDetails: "+event.error.details); 
		} 
		
		public function set_details(projectXML)
		{
			trace(projectXML.id);
			id = projectXML.id;
			name = projectXML.name;
			datestart = projectXML.datestart;
			dateend = projectXML.dateend;
			active = projectXML.active;
			billable = projectXML.billable;
			clientid = projectXML.clientid;
			client = projectXML.client;
			localid = projectXML.localid;
		}
		
	}
}